http://www.BillHowell.ca/LibreOffice/LibreCalc bank account macro system.txt /media/bill/PROJECTS/Investments/LibreCalc bank account macro system.txt www.BillHowell.ca ?14Nov2017? initial but FAR earlier prototypes (<2003?)!! These were application-specific. 26Aug2018 extensive upgrade with auto-keyword extraction, CategorySetUp sheet *************************** Table of Contents Introduction, system description Nomenclature Routine use and adaptation of the system Recalculation - is set to Manual, to avoid annoying time-delays Copy-paste new transactions from online reports Remove images from the Category_sheet_name ("transactions") sheet remove \n (newlines) from text cells [KeyWord, CatWord] table - add another item to the list : CatWord list - for lines that have no "Category_set" CatWord : Check that the macro is working Installation of the system [Download, adapt] the example file "00_Chinook.ods" : Adapt an existing workbook : Copy the CategorySetUp sheet of "00_Chinook.ods" to your own workbook Change the entries in the [KeyWord, CatWord] table, as required by your [system, workbook] Install the macros Install the "Howell_extract_codes_macLib" : Install the "Howell_LibreCalc_remove_images_macLib", or [use, adapt] many online macros for that purpose : Update your LibreCalc macros : Handy references Missing capabilities, some thoughts for upgrades Pretty button to launch the macro Breakdown of items using receipts Multiple columns of text with KeyWords Conflicting KeyWord classifications Multiple "transaction" sheets in a workbook Inferred KeyWords Multiple classes and inheritance Edit this system, and create your own macros Don't wait for upgrades On my computer, the Table of Contents is generated by Linux bash commands : $ grep ">>> " "/media/bill/PROJECTS/Investments/LibreCalc bank account macro system.txt" | sed 's/>>>/ /g' Then copy the list from the terminal to replace the list above. Adapt this to your own system utilities and pathway... *************************** >>> Introduction, system description The "LibreCalc bank account macro system" is a [small, simple (2 day to write, document), general, easily apdaptable] system to semi-automatically assign [KeyWords, CatWords] (classes or categories) to rows of data that are copy-pasted from online reports. The example provided is a my monthly spending bank account, which receives intentionally small amounts monthly to force me to control my spending (not so successfully). Automatic [KeyWords, CatWords] are generated based on exact text phrases that appear in a column of electronic bank reports. But these automatic results are easily over-ridden manually assigning KeyWord, without corrupting the regeneration of [KeyWods, CatWords] for all transactions by the macro. Usually, we purchase many items in a store, and these should be assigned to differnet classes. But there's no way that I'm going to type in details from hundreds of receipts per year, so this is a good compromise for my genereal needs (to track spending). This system can be done via normal cell formulae. But the intent here is to show a macro implementation, which produces smaller file sizes, and more [regular, simple, sure] analysis. However, in usuing macros, huge strengths of spreadsheets are lost, for example : - spreadsheets often have many custom calculations throughout, which are REQUIRED to properly analyse data - formulae use cell referencing which is [easy, intuitive, powerful], in contrast to the [fragile, constrained, brittle] programming in macros This document is a [DRAFT, incomplete] guide to using the "LibreCalc bank account macro system", which I may leave as it is for now (several years or forever). Hopefully it will be of some help. >>>>>> Nomenclature As the example is based on tracking a bank account, much of the terminology in this file is based on "transactions". However, my intent and earlier coding was for completely different purposes such as [mass email lists, time logs, etc]. This "LibreCalc [semi-auto-classification, analysis] macro system" transactions Category_table the table with all of the transactions. *************************** >>> Routine use and adaptation of the system >>>>>> Recalculation - is set to Manual, to avoid annoying time-delays I hate waiting for recalc in a large spreadsheet while I do simple work. Do I turn off the recalc, and press F9 when I want items updated. I also forgete to press F9, and freak out when numbers don't change, but this comes with the territory. Recalc doe NOT affect the macros in this system. It DOES affect the summary table (which currently uses simple formulae). >>>>>> Copy-paste new transactions from online reports Add sufficient new rows to the Category_table so so that you DON'T OVER-WRITE the last line of the table!!!! In LibreCalc, set the "custom" and "Convert text to [numbers, dates, ??]" or something like that. This is important, as it's a pain in the butt to convert old dates that appear as text in a sheet! >>>>>> Remove images from the Category_sheet_name ("transactions") sheet One of my bank accounts inserts images into the Category_sheet_name sheet. You'll know when images are in the Category_sheet_name sheet, because everything slows down, sometimes to a halt with freezes that can take minutes. While one can avoid that by copying the transactions to a text file, THEN pasting to the sheet, this often has bad consequences (eg cell with multiple text lines). So I simply copy-paste from online statements. To remove, install and use the "Howell_LibreCalc_remove_images_macLib", or [use, adapt] many online macros for that purpose. It works fine for me, so I didn't write any [explanations, instructions]. As always, make sure you backup yor files before using the first time! >>>>>> remove \n (newlines) from text cells Text lines interfere with KeyWord search. I simply use Ctrl-H (or LibreCalc Menue -> Find & Replace) : search for : \n replace with : (leave this blank!) Other options : regular expressions Then click "Replace All". I you have line returns in your Category_sheet_name sheet (eg "transactions") you will have to do a more careful fix! >>>>>> [KeyWord, CatWord] table - add another item to the list : Simply insert a row in the [KeyWord, CatWord] table (Category_keyWord_table), and type in the addition. Make sure that you don't corrupt the title or ending lines! (or you must redefine the range for the Category_keyWord_table.) >>>>>> CatWord list - for lines that have no "Category_set" CatWord : It's not worth adding every supplier to the KeyWord list, so one may make a judgement call as to which suppliers should be added to the CatWord list - perhaps based on long-term [historical, current, future expected] appearance in the "transactions", or whether [one-time, important but infrequent] entries need to be highlighted. For suppliers that won't be added to the KeyWord list, simply copy one of the "CatWords" below and paste it to the Category_manual column for a transactiont. To view all transactions that do NOT have a designated "CatWord", simple filter the Category_set column with the "empty" option. This makes it very easy to scroll down the list and assign a CatWord to the Category_manual column. Update the CatWords list below by [sect,copy] the CatWord column, and pasting it below to update this file. Dunno - If your memory and record tracking is as bad as mine, it's sometimes not obvious what a [supplier, purchase] is. The "dunno" CatWord colelcts these so they are reported. CatWords to copy-paste : ATM Withdrawal CHINOOK ATM Withdrawal Other books car maintenance Cheque clothes computer Credit Interest deposit dunno e-Transfer - Credit exercise fees gasoline groceries haircut hardware Insufficient Funds Charge junk food live education live entertainment Maintenance Service Charge medical-dental paperStuff pharmacy poison resto-bars video-cinema Walmart The CatWords list above is generated by : copy CatWord column in CategorySetUp sheet paste to "/media/bill/PROJECTS/Investments/LibreCalc bank account macro system - catWord list.txt" by REPLACING entire contents of that file. $ sort --unique "/media/bill/PROJECTS/Investments/LibreCalc bank account macro system - catWord list.txt" This produces terminal screen output $ copy "terminal screen list of catWords" to this file for future working reference Obviously : - you MUST be using a Linux bash terminal, and you have to replace "/media/bill/PROJECTS/Investments/" with the directory path where you want the file "LibreCalc macro Howell_extract_codes - catWord list.txt" stored. - for one or two changes, simply [add, delete, correct] items in the above list by copy-paste or typing >>>>>> Check that the macro is working You can see if the macro is working properly by : 1. select BOTH [Category_set, Category_auto] data : a) position the cursor starting in the cell one row BELOW the Category_set title in the Category_table b) holding the Control key (Ctrl) down, right arrow to include the Category_auto column c) still holding down Ctrl-key, page down until you encounter the last data entry d) Make sure that you DO NOT include the Category_manual column, or that custom-entered data will be lost!! 2. delete the selection 3. run the macro ******************************** >>> Installation of the system >>>>>> [Download, adapt] the example file "00_Chinook.ods" : download the file "http://www.BillHowell.ca/LibreOffice/00_Chinook.ods" to your directory that hosts your [existing, intended] workbook >>>>>> Adapt an existing workbook : As always, make sure you backup your files before making any of the changes below!!!! I think we all often ignore this [simple, important] point - until the next time we lose [valuable, big time investment] information. You can www.pitonyak.org/AndrewMacro.odteither adapt this workbook to your own use, or modify an existing workbook that you have. >>>>>>>>> Copy the CategorySetUp sheet of "00_Chinook.ods" to your own workbook Check the updated range names (LibreCalc Menu -> Insert -> Names -> Organise). As any carried-over range definitions will likely be incorrect, you will have to [add, edit] all range names in the "Mandatory range names" list of the CategorySetUp sheet. If you happen to have range names that are the SAME as the KeyWord range names (not so likely?), OR the KeyWord range names (much more likely!), then you will have to make changes. Good luck - but if you're learning macro programming, it will be a good exercise, otherwise it's a tedious pain. Note that you can ADD range names that [coincide with, overlap] range names in your current workbook. DON'T delete existing rangenames to replace them with the Category ranges - you will lose current functionality that you have built into your workbook. >>>>>>>>> Change the entries in the [KeyWord, CatWord] table, as required by your [system, workbook] This should be both straightforward and tedious. >>>>>> Install the macros The details below are ultra-easy for me to forget. Be patient - mistakes are frustrating, albeit usually not damaging. >>>>>>>>> Install the "Howell_extract_codes_macLib" : download the directory "http://www.BillHowell.ca/LibreOffice/Howell_extract_codes" to your "" where in my case = "/home/bill/.config/libreoffice/4/user/basic/" >>>>>>>>> Install the "Howell_LibreCalc_remove_images_macLib", or [use, adapt] many online macros for that purpose : download the directory "http://www.BillHowell.ca/LibreOffice/Howell_LibreCalc_remove_images_macLib" to your "" where in my case = "/home/bill/.config/libreoffice/4/user/basic/" Alternately - you can [use, adapt] many online macros for that purpose. >>>>>>>>> Update your LibreCalc macros : Without this step, the new macros won't be visible to your system. Here's my old "generic" description of the process : Creating a new macro (using an example Howell_IEEE_prohibited_macLib) NOTE: I NEED macros to be in /home/bill/.config/libreoffice/4/user/basic That way my weekly backups automatically catch them!! 1. CLOSE all instances of LibreOffice 2. EDIT LibreCalc Basic script file : /home/bill/.config/libreoffice/4/user/basic/script.xlc -> copy an existing line with a maLib, eg : copy to 3. COPY an existing macro directory and rename to new macro, eg. : copy /home/bill/.config/libreoffice/4/user/basic/Howell_ToDos_macLib to /home/bill/.config/libreoffice/4/user/basic/Howell_IEEE_prohibited_macLib 4. EDIT /home/bill/.config/libreoffice/4/user/basic/Howell_IEEE_prohibited_macLib/dialog.xlb change line : to line : 5. EDIT /home/bill/.config/libreoffice/4/user/basic/Howell_IEEE_prohibited_macLib/script.xlb change lines : to lines : 6. CHANGE filename of script file : from : /home/bill/.config/libreoffice/4/user/basic/Howell_IEEE_prohibited_macLib/Howell_ToDos_script.xba to : /home/bill/.config/libreoffice/4/user/basic/Howell_IEEE_prohibited_macLib/Howell_IEEE_prohibited_script.xba 7. EDIT & CONVERT the old macro to the new macro (old code can be handy as a template) : Libre[Write,Calc] Menu -> Tools -> Macros -> Organize Macros /home/bill/.config/libreoffice/4/user/basic/Howell_IEEE_prohibited_macLib/Howell_IEEE_prohibited_script.xba 8. No need to backup all macro directories - this is done by regular weekly and monthly backups I should adapt the above instructions to this "LibreCalc bank account macro system", but maybe much later ... ******************************** >>> Handy references To see standard macros : LibreOffice Menu -> LibreOffice Help -> Contents -> Macros and Programming -> Command reference -> Run-Time Functions, Statements, and Operators -> select theme etc BEST REFERENCE for OpenOffice macros : http://api.libreoffice.org/ This is a great reference. www.pitonyak.org/AndrewMacro.odt Also good - very good for some troublesome issues http://www.openoffice.org/documentation/manuals/userguide3/0312CG3-CalcMacros.pdf - kind of Mickey Mouse Libre Office Basic Guide - you can web-search this The blogs are great!! just search for your problem, for example : Search "LibreOffice Calc and macro to get the FORMULA of a cell" https://ask.libreoffice.org/en/question/77399/setting-a-formula-in-macro-basic/ answered Sep 15 '16, Ratslinger The formula is a string, opened and closed by a quote. In Basic quotes contained within must be doubled sheet.getCellByPosition(3,16).setFormula("=IF(B17="""";"""";1)") search "LibreOffice and how do I shut off the warning about macros when a document is loading?" search "LibreOffice and how do I turn off update of links?"" Many of my own trials and tribulations are somewhat-documented in : download the file "http://www.BillHowell.ca/LibreOffice/0_LibreOffice macros.txt" to your directory that hosts your LibreOffice or other help files *************************** >>> Missing capabilities, some thoughts for upgrades >>>>>> Pretty button to launch the macro I don't normally install buttons to launch macros, unless I use the macros daily or weekly. Any [project, admin] use of this "LibreCalc bank account macro system" probably won't be any more frequent than every 6 months, so I won't waste my time on a button (OK - not much time, but not much value either). But this would be a [quick, easy] project for you, especially if you are just learning how to do macros. >>>>>> Breakdown of items using receipts Usually, we purchase many items in a store, and these should be assigned to differnet classes. But there's no way that I'm going to type in details from hundreds of receipts per year, so this is a good compromise for my general needs (to track spending). A compromise approach could be to assign set percentages to a set of CatWords for each "supplier" (KeyWord). My guess is that wouldn't be too hard, but it would complicate the macro significantly. The upside is that it is actually needed for better accuracy (may someday I'll really need this enough to implement that capability...). >>>>>> Multiple columns of text with KeyWords Currently, only two columns (Category_comments) are searched for keyWord text. But in several applications I've done, keywords might appear in any of five or six text columns. That basic macro modification (for example checking the Comments" column in the chequebook example) would be easy, but I won't do that until I need it. >>>>>> Conflicting KeyWord classifications In the current system, a KeyWord is assigned based on the order of the KeyWords list : as soon as a keywork is encountered, it becomes the KeyWord, which then defines the CatWord. But what about two or more keywords in the text? >>>>>> Multiple "transaction" sheets in a workbook You often want to keep all "transaction" sheets related to a system in the same workbook (file). For example, in an accounting system there might be dozens or hundreds of electronic statements to track. This "LibreCalc bank account macro system" is NOT well suited to doing that other than by having dozens or hundreds of workbooks, but if on 2 to 5 "transaction" sheets are involved, then there is are several simple options for doing so. t One simple possibility (that I haven't tried), is to : 1. create one "CategorySetUp" sheet for each "statements". For example : [CategorySetup_chequing1, CategorySetup_chequing2, CategorySetup_investment1, CategorySetup_creditCard1]. 2. Next, create a "summary" sheet for each, OR combine all sources into one "summary" sheet. You could then analyse one "CategorySetUp" source at a time by either, for example : - renaming the "CategorySetup_investment1" sheet to "CategorySetup". You then do the normal [update, analysis], and when finished, rename the "CategorySetup" sheet to "CategorySetup_investment1". - copying the "CategorySetup_investment1" sheet to "CategorySetup". While this is safer than the first option, you stand to lose changes, especially additions of KeyWords. - write a macro to automatically switch between sources. - other ideas (I haven't thought much about this) >>>>>> Inferred KeyWords This "LibreCalc bank account macro system" relies on exact keywords, even if in many cases several exact KeyWords appear for the same "supplier". But for many of my applications, KeyWords are NOT [explicit, reliable], so some way of inferrring them is required. In some cases (such as the mass email system), it would be nice to have advanced inference using Support Vector Machines (SVMs), Extreme Learning Machines (ELMs), or many, many other approaches. Even though neural networks are my #1 priority interest, I haven't actually done that. My approach would be to use a hybrid system for inference - with inference done by my own programming in a general language (QNial) or application for that. I would then past KeyWords to the KeyWord list in the CategorySetUp sheet. In extreme cases (such as email classification in the mass email system I use), manual intervention, assisted by automated filtering or selection of some sort, is required. While some "fuzziness" is normal in accounting systems, for example (in contrast to claims to the contrary) >>>>>> Multiple classes and inheritance In some cases I've deliberately built systems with multiple [perspectives, classes]. One example was for data from an accounting system at work. While accounts were setup as "strict" classifiers, data usage, depending on usage, required different and multiple classifications. This naturally breaks the "basis" of normal accounting, but allows one to get "real world" analysis that accounting systems can't easily provide. There is nothing for this in this "LibreCalc bank account macro system". >>>>>> Edit this system, and create your own macros Every application is different, and no doubt you will want to modify the system so that it is better adapted to your needs. I hope that it is a good excersise for those learning LibreCalc basic macro programming. >>>>>> Don't wait for upgrades It took me >20 years to spend two days to do the 26Aug2018 upgrade. Don't hold your breath waiting for me to add further upgrades. This has not been a priority for me... # enddoc